/*
AlliedBridge Content Management System
Copyright (C) 2006 AlliedBridge

This program is free software; you can redistribute it and/or modify it under the
terms of the GNU General Public License as published by the Free Software Foundation;

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the
Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.volume4.alliedbridge.tools;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import javax.servlet.ServletContext;
import java.io.IOException;
import java.sql.*;

/**
 * Created by IntelliJ IDEA.
 * User: Schalk
 * Date: 2006/02/19
 * Time: 11:34:50
 * To change this template use File | Settings | File Templates.
 */

public class UpdateCup extends HttpServlet {

    // Should the following three String declarations be private or final?
    // Should they be in an interface?
    public String DRIVER, URL, USER, PASS;
    public String messageSuccess, messageFailed, app_root, cup, mem_number;
    int age;

    public void init() throws ServletException {
        ServletContext context = getServletContext();
        DRIVER = context.getInitParameter("driver");
        URL = context.getInitParameter("mc_dburl");
        USER = context.getInitParameter("mc_dbuser");
        PASS = context.getInitParameter("mc_dbpass");
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
        Connection con = null;
        // These need to be moved to a properties file.
        messageSuccess = "Appropriate cup level added to all members.";
        // For failed messages more information should be provided.
        // For example a formatted stackTrace() or system message.
        messageFailed = "Operation failed.";

        try {
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL, USER, PASS);

            String getAge = "SELECT mem_number, age FROM ab_members";

            PreparedStatement age_stmt = con.prepareStatement(getAge);
            ResultSet rs = age_stmt.executeQuery();

            boolean hasData = false;
            while(rs.next()) {
                hasData=true;

                mem_number = rs.getString(1);
                age = rs.getInt(2);

                if(age < 13) {
                    cup = "kids";
                } else if ((age >= 13) && (age <= 16)) {
                    cup = "teen";
                } else {
                    cup = "open";
                }

            String query = "UPDATE ab_members SET cup= '" + cup + "' " +
                           "WHERE mem_number ='" + mem_number + "'";

            PreparedStatement stmt = con.prepareStatement(query);
            int result = stmt.executeUpdate(query);
           }
           app_root = request.getContextPath();
           response.sendRedirect(app_root + "/application/application_messages/process_result.jsp?message=" + messageSuccess);

           if(!hasData) {
               response.sendRedirect(app_root + "/application/application_messages/process_result.jsp?message=" + messageFailed);
           }

        } catch (SQLException ex) {
            System.out.println("\nERROR:------ SQLException -----\n");
            while (ex != null) {
                System.out.println("Message: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("ErrorCode :" + ex.getErrorCode());
                ex = ex.getNextException();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ex) {
                System.out.println("\nERROR:------ SQLException -----\n");
                System.out.println("Message: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("ErrorCode :" + ex.getErrorCode());
            }
        }
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }
}